package cn.jsj.dao;

import java.util.LinkedList;
import java.util.List;
import java.util.ListIterator;

import cn.jsj.pojo.TblBankAccount;

/**
 * 银行帐处理类
 * @author Administrator
 *
 */
public class TblBankAccountDAO {

	/**
	 * 获得银行帐信息条数
	 * @return：银行帐信息总条数
	 */
	public int getBankAccountNumb(){
		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		String hql="select count(*) from TblBankAccount tba";
	    List result=session.createQuery(hql)
		.list();
		// 提交事务
		tx.commit();
		session.close();
		//获得结果的迭代器
		 ListIterator iterator = result.listIterator();
		 Long rows =(Long)iterator.next();
		 return Integer.parseInt(rows.toString());
	}

	/**
	 * 获得某个页面的银行帐信息
	 * @param startNumb：从第几条开始
	 * @param totalNumb：总共几条
	 * @return：银行帐信息队列
	 */
	public java.util.List<TblBankAccount> getBankAccount(int startNumb,int totalNumb){
		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		List result=session.createCriteria(TblBankAccount.class)
		.addOrder(org.hibernate.criterion.Order.desc("bankAccTime"))
		.setFirstResult(startNumb)
		.setMaxResults(totalNumb)
		.list();
		// 提交事务
		tx.commit();
		session.close();
		return result;
	}

	/**
	 * 保存一条银行帐信息
	 * @param tba：银行帐信息对象
	 * @return：1：保存成功
	 */
	public int saveBankInfo(cn.jsj.pojo.TblBankAccount tba){

		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		session.save(tba);
		// 提交事务
		tx.commit();
		session.close();
		return 1;
	}

	/**
	 * 获得符合搜索条件的银行帐条数
	 * @param sbp：后台与DB进行交互的对象
	 * @return：银行帐信息条数
	 */
	public int getSearchBankNumb(cn.jsj.bgtodb.SearchBankProperty sbp){

		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		List result=new LinkedList();
		//如果用户没有输入月份
		if(sbp.getBankMonth().length()==0){
			//根据输入的字符串构造出要搜索的时间段
			String begin=sbp.getBankYear()+"-01"+"-01";
			java.util.Date beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
			String end=sbp.getBankYear()+"-12"+"-31";
			java.util.Date endDate=cn.jsj.utils.TimeDispose.transferDate(end);
			//查询在这个时间段记录的条数
			String hql="select count(*) from TblBankAccount tba where tba.bankAccTime between?and?";
		    result=session.createQuery(hql)
			.setDate(0, beginDate)
			.setDate(1, endDate)
			.list();
		    // 提交事务
			tx.commit();
			session.close();

			//获得结果的迭代器
			 ListIterator iterator = result.listIterator();
			 Long rows =(Long)iterator.next();
			 return Integer.parseInt(rows.toString());

		}
		//如果输入了月份
		else{
			//根据输入的字符串构造出要搜索的时间段
			String begin=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-01";
			java.util.Date beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
			//判断这个月有没有31号
			int month=Integer.parseInt(sbp.getBankMonth());
			//结束日子
			String end="";
			if(month<8&&month%2==1){
				 end=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-31";
			}
			if(month<8&&month%2==0)
			{
				 end=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-30";
			}
			if(month>=8&&month%2==0){
				 end=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-31";
			}
			if(month>=8&&month%2==1){
				end=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-30";
			}
			java.util.Date endDate=cn.jsj.utils.TimeDispose.transferDate(end);
			//查询在这个时间段记录的条数
			String hql="select count(*) from TblBankAccount tba where tba.bankAccTime between?and?";
		    result=session.createQuery(hql)
			.setDate(0, beginDate)
			.setDate(1, endDate)
			.list();
		    // 提交事务
			tx.commit();
			session.close();

			//获得结果的迭代器
			 ListIterator iterator = result.listIterator();
			 Long rows =(Long)iterator.next();
			 return Integer.parseInt(rows.toString());
		}
		}



	/**
	 * 获得符合搜索条件的银行帐信息
	 * @param sbp：后台与DB进行交互的对象
	 * @return：银行帐信息队列
	 */
	public java.util.List<TblBankAccount> getSearchBankInfo(cn.jsj.bgtodb.SearchBankProperty sbp){

		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		//搜索结果队列
		//判断是否输入了月份
		List result=new LinkedList();
		if(sbp.getBankMonth().length()==0){
			String begin=sbp.getBankYear()+"-01"+"-01";
			java.util.Date beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
			String end=sbp.getBankYear()+"-12"+"-31";
			java.util.Date endDate=cn.jsj.utils.TimeDispose.transferDate(end);

			result=session.createCriteria(TblBankAccount.class)
			.add(org.hibernate.criterion.Restrictions.between("bankAccTime", beginDate, endDate))
			.addOrder(org.hibernate.criterion.Order.desc("bankAccTime"))
			.setFirstResult(sbp.getStartNumb())
			.setMaxResults(sbp.getTotalNumb())
			.list();
		}else{
			String begin=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-01";
			java.util.Date beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
			//判断这个月有没有31号
			int month=Integer.parseInt(sbp.getBankMonth());

			//结束日子
			String end="";
			if(month<8&&month%2==1){
				 end=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-31";
			}
			if(month<8&&month%2==0)
			{
				 end=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-30";
			}
			if(month>=8&&month%2==0){
				 end=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-31";
			}
			if(month>=8&&month%2==1){
				end=sbp.getBankYear()+"-"+sbp.getBankMonth()+"-30";
			}
			java.util.Date endDate=cn.jsj.utils.TimeDispose.transferDate(end);
			result=session.createCriteria(TblBankAccount.class)
			 .add(org.hibernate.criterion.Restrictions.between("bankAccTime", beginDate, endDate))
			 .addOrder(org.hibernate.criterion.Order.desc("bankAccTime"))
				.setFirstResult(sbp.getStartNumb())
				.setMaxResults(sbp.getTotalNumb())
				.list();
		}
		// 提交事务
		tx.commit();
		session.close();

			return result;
		}



	/**
	 * 获得报表信息
	 * @param brf：报表信息参数类
	 * @return：报表信息字符串 0:支出收入都为NULL
	 */
	public String getReportFormData(cn.jsj.bgtodb.ReportForm brf){

		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		//判断报表类型
		int catalogNumb=Integer.parseInt(brf.getCatalogName());
		//查询结果
		List result;
		//如果为年报表
		if(catalogNumb==1){
			String begin=brf.getDetailTime()+"-01"+"-01";
			java.util.Date beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
			String end=brf.getDetailTime()+"-12"+"-31";
			java.util.Date endDate=cn.jsj.utils.TimeDispose.transferDate(end);
			String hql="select sum(tba.incomeMoney),sum(tba.payoutMoney) from TblBankAccount tba where tba.bankAccTime between?and?";
		    result=session.createQuery(hql)
			.setDate(0, beginDate)
			.setDate(1, endDate)
			.list();
		    ListIterator iterator = result.listIterator();
		    Object[] rows = (Object[]) iterator.next();
		    //判断是否为NULL
		    if(rows[0]==null){
		    	return 0+"";
		    }
		    else{
		    	return rows[0].toString()+","+rows[1].toString();
		    }

		}
		//如果为季度报表
		if(catalogNumb==2){
			//判断为哪个季度
			int quarterLabel=Integer.parseInt(brf.getDetailTime());

			//开始结束时间
			java.util.Date beginDate=new java.util.Date();
			java.util.Date endDate=new java.util.Date();

			//如果为第一季度
			if(quarterLabel==1){
				String begin=cn.jsj.utils.TimeDispose.getYear()+"-01"+"-01";
				 beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
				String end=cn.jsj.utils.TimeDispose.getYear()+"-03"+"-31";
				 endDate=cn.jsj.utils.TimeDispose.transferDate(end);
			}
			if(quarterLabel==2){
				String begin=cn.jsj.utils.TimeDispose.getYear()+"-04"+"-01";
				 beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
				String end=cn.jsj.utils.TimeDispose.getYear()+"-06"+"-30";
				 endDate=cn.jsj.utils.TimeDispose.transferDate(end);
			}
			if(quarterLabel==3){
				String begin=cn.jsj.utils.TimeDispose.getYear()+"-07"+"-01";
				 beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
				String end=cn.jsj.utils.TimeDispose.getYear()+"-09"+"-30";
				 endDate=cn.jsj.utils.TimeDispose.transferDate(end);
			}
			if(quarterLabel==4){
				String begin=cn.jsj.utils.TimeDispose.getYear()+"-10"+"-01";
				 beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);
				String end=cn.jsj.utils.TimeDispose.getYear()+"-12"+"-31";
				 endDate=cn.jsj.utils.TimeDispose.transferDate(end);
			}

			String hql="select sum(tba.incomeMoney),sum(tba.payoutMoney) from TblBankAccount tba where tba.bankAccTime between?and?";
		    result=session.createQuery(hql)
			.setDate(0, beginDate)
			.setDate(1, endDate)
			.list();
		    if(result.size()!=0){
		    ListIterator iterator = result.listIterator();
		    Object[] rows = (Object[]) iterator.next();
		    //判断是否为NULL
		    if(rows[0]==null){
		    	return 0+"";
		    }
		    else{
		    	return rows[0].toString()+","+rows[1].toString();
		    }
		    }
		}

		//如果为月报表
		if(catalogNumb==3){
			//获得月份
			int month=Integer.parseInt(brf.getDetailTime());

			String begin=cn.jsj.utils.TimeDispose.getYear()+"-"+brf.getDetailTime()+"-01";
			java.util.Date beginDate=cn.jsj.utils.TimeDispose.transferDate(begin);

			//结束日子
			String end="";
			if(month<8&&month%2==1){
				 end=cn.jsj.utils.TimeDispose.getYear()+"-"+brf.getDetailTime()+"-31";
			}
			if(month<8&&month%2==0)
			{
				 end=cn.jsj.utils.TimeDispose.getYear()+"-"+brf.getDetailTime()+"-30";
			}
			if(month>=8&&month%2==0){
				 end=cn.jsj.utils.TimeDispose.getYear()+"-"+brf.getDetailTime()+"-31";
			}
			if(month>=8&&month%2==1){
				end=cn.jsj.utils.TimeDispose.getYear()+"-"+brf.getDetailTime()+"-30";
			}
			java.util.Date endDate=cn.jsj.utils.TimeDispose.transferDate(end);

			String hql="select sum(tba.incomeMoney),sum(tba.payoutMoney) from TblBankAccount tba where tba.bankAccTime between?and?";
		    result=session.createQuery(hql)
			.setDate(0, beginDate)
			.setDate(1, endDate)
			.list();
		    if(result.size()!=0){
		    ListIterator iterator = result.listIterator();
		    Object[] rows = (Object[]) iterator.next();
		    //判断是否为NULL
		    if(rows[0]==null){
		    	return 0+"";
		    }
		    else{
		    	return rows[0].toString()+","+rows[1].toString();
		    }
		    }
		}
		// 提交事务
		tx.commit();
		session.close();
		return 0+"";
	}

	/**
	 * 获得银行帐总资金
	 * @return：总资金
	 */
	public int getTotalBankMoney(){
		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		String hql="select sum(tba.incomeMoney),sum(tba.payoutMoney) from TblBankAccount tba";
		List result=session.createQuery(hql).list();
		// 提交事务
		tx.commit();
		session.close();
		ListIterator iterator = result.listIterator();
	    Object[] rows = (Object[]) iterator.next();
	    if(rows[0]==null){
	    	return 0;
	    }else{
	    	int income=Integer.parseInt(rows[0].toString());
	    	int payout=Integer.parseInt(rows[1].toString());
	    	int leftMoney=income-payout;
	    	return leftMoney;
	    }
	}

	/**
	 * 获取时间段银行帐资金情况
	 * @param stmp：参数类
	 * @return
	 */
	public String getTimeBankMoney(cn.jsj.bgtodb.SomeTimeMoneyProperty stmp){
		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		String hql="select sum(tba.incomeMoney),sum(tba.payoutMoney) from TblBankAccount tba where tba.bankAccTime between?and?";
		List result=session.createQuery(hql)
		.setDate(0, stmp.getStartTime())
		.setDate(1, stmp.getEndTime())
		.list();
		// 提交事务
		tx.commit();
		session.close();
		ListIterator iterator = result.listIterator();
	    Object[] rows = (Object[]) iterator.next();
	    if(rows[0]==null){
	    	return "0,0,0";
	    }else{
	    	int income=Integer.parseInt(rows[0].toString());
	    	int payout=Integer.parseInt(rows[1].toString());
	    	int leftMoney=income-payout;
	    	return income+","+payout+","+leftMoney;
	    }
	}

	/**
	 * 更新银行帐信息
	 * @param tba：银行帐信息对象
	 * @return：1：更新成功
	 */
	public int updateBankInfo(cn.jsj.pojo.TblBankAccount tba){

		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		session.update(tba);
		// 提交事务
		tx.commit();
		session.close();
		return 1;
	}

	/**
	 * 获取图形化报表数据
	 * @param catalogId：报表类型
	 * @return：报表数据
	 */
	public String getFigureChartData(int catalogId){

		// 取得session对象
		org.hibernate.Session session = HBUtils.getSession();
		// 新建事务对象
		org.hibernate.Transaction tx = session.beginTransaction();
		// 开始事务
		tx.begin();
		//hql语句
		String hql="";
		//如果为月度纯收入报表查询
		if(catalogId==1){
		 hql="select month(tba.bankAccTime), sum(tba.incomeMoney)from TblBankAccount tba where year(tba.bankAccTime)=? group by month(tba.bankAccTime)";
		}
		//如果为月度支出报表查询
		if(catalogId==2){
			hql="select month(tba.bankAccTime), sum(tba.payoutMoney)from TblBankAccount tba where year(tba.bankAccTime)=? group by month(tba.bankAccTime)";
		}
		//如果为月度汇总报表查询
		if(catalogId==3){
			hql="select month(tba.bankAccTime), (sum(tba.incomeMoney)-sum(tba.payoutMoney))from TblBankAccount tba where year(tba.bankAccTime)=? group by month(tba.bankAccTime)";
		}
		//如果为季度报表
		if(catalogId==4){
			hql="select quarter(tba.bankAccTime), (sum(tba.incomeMoney)-sum(tba.payoutMoney))from TblBankAccount tba where year(tba.bankAccTime)=? group by quarter(tba.bankAccTime)";
		}
		//如果为年度报表
		if(catalogId==5){
			hql="select year(tba.bankAccTime), (sum(tba.incomeMoney)-sum(tba.payoutMoney))from TblBankAccount tba group by year(tba.bankAccTime)";
		}
		//获得今年的年份
		int year=cn.jsj.utils.TimeDispose.getYear();

		//结果队列
		List result=new LinkedList();
		//如果不为年度报表查询，需要限制年份!
		if(catalogId!=5){
		 result=session.createQuery(hql)
					.setInteger(0, year)
					.list();
		}else{
			result=session.createQuery(hql)
			.list();
		}
		// 提交事务
		tx.commit();
		session.close();
		ListIterator iterator = result.listIterator();

		//结果字符串
		java.lang.StringBuilder resultString=new java.lang.StringBuilder();
		resultString.append("[");
		//月 季度 年标志
		String timeLabel="";
		//季报表
		if(catalogId==4){
			timeLabel="季度";
		}else{
			//年报表
			if(catalogId==5){
				timeLabel="年";
			}else{
				timeLabel="月份";
			}
		}

		//循环取出数据
		while(iterator.hasNext()){
	    Object[] rows = (Object[]) iterator.next();
	    resultString.append("['"+rows[0].toString()+timeLabel+"',"+rows[1].toString()+"],");
	}
		//去掉最后面一个逗号
		String chartString=resultString.substring(0, resultString.length()-1);
		chartString=chartString+"]";
		return chartString;
	}

}